﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using SapB1WebApp.Utils;

namespace SapB1WebApp.DAL
{
    public class SQLConnection
    {
        private SqlConnection sConn;
        public bool bConnect;
        private string companyDB = string.Empty;
        public SQLConnection(string companyDB)
        {
            this.companyDB = companyDB;
        }
        public string setConnectionStringDB()
        {
            try
            {
                string SQLUser = null;
                string SQLPwd = null;
                string SQLServer = null;
                string strConnect = string.Empty;
                string sCon = string.Empty;
                string[] connectStringArr;
                strConnect = "DBConnect";
                sCon = System.Configuration.ConfigurationManager.AppSettings.Get(strConnect);
                connectStringArr = sCon.Split(';');
                if (connectStringArr.Length > 0)
                {
                    SQLServer = connectStringArr[0].ToString();
                    SQLUser = connectStringArr[1].ToString();
                    SQLPwd = connectStringArr[2].ToString();
                    sCon = "server= " + SQLServer + ";database=" + companyDB + " ;uid=" + SQLUser + "; pwd=" + SQLPwd + ";";
                    sConn = new SqlConnection(sCon);
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
            return string.Empty;
        }
        private SqlConnection connectionDatabase()
        {
            if (sConn.State == ConnectionState.Open)
            {
                sConn.Close();
            }
            try
            {
                sConn.Open();
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
            }
            return sConn;
        }
        public DataSet Excute_Query(string queryString, List<object> paramsArrays)
        {
            try
            {
                string strCon = "";
                string errMsg = this.setConnectionStringDB();
                if (errMsg.Length == 0)
                {
                    SqlConnection myConn = this.connectionDatabase();
                    strCon = myConn.ConnectionString;
                    SqlCommand MyCommand = new SqlCommand(queryString, myConn);
                    MyCommand.CommandType = CommandType.Text;
                    SqlDataAdapter da = new SqlDataAdapter();
                    DataSet mytable = new DataSet();
                    da.SelectCommand = MyCommand;
                    if (paramsArrays != null)
                    {
                        for (int i = 0; i <= paramsArrays.Count - 1; i++)
                        {
                            MyCommand.Parameters.AddWithValue(string.Format("{0}{1}", "@Param", i + 1), paramsArrays[i]);
                        }
                    }
                    da.Fill(mytable);
                    myConn.Close();
                    return mytable;
                }
                return null;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet Excute_Query_Stored_Procedure(string storedProcedure, List<object> paramsArrays)
        {
            try
            {
                string strCon = "";
                string errMsg = this.setConnectionStringDB();
                if (errMsg.Length == 0)
                {
                    SqlConnection myConn = this.connectionDatabase();
                    strCon = myConn.ConnectionString;
                    SqlCommand MyCommand = new SqlCommand(storedProcedure, myConn);
                    MyCommand.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter da = new SqlDataAdapter();
                    DataSet mytable = new DataSet();
                    da.SelectCommand = MyCommand;
                    for (int i = 0; i <= paramsArrays.Count - 1; i++)
                    {
                        MyCommand.Parameters.AddWithValue(string.Format("{0}{1}", "@Param", i + 1), paramsArrays[i]);
                    }
                    da.Fill(mytable);
                    myConn.Close();
                    return mytable;
                }
                return null;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public int Execute_Non_Query(string excuteQuery, List<object> paramsArrays)
        {
            try
            {
                string errMsg = this.setConnectionStringDB();
                if (errMsg.Length == 0)
                {
                    SqlConnection myConn = this.connectionDatabase();
                    SqlCommand MyCommand = new SqlCommand(excuteQuery, myConn);
                    MyCommand.CommandType = CommandType.Text;
                    for (int i = 0; i <= paramsArrays.Count - 1; i++)
                    {
                        MyCommand.Parameters.AddWithValue(string.Format("{0}{1}", "@Param", i + 1), paramsArrays[i]);
                    }
                    int count = MyCommand.ExecuteNonQuery();
                    myConn.Close();
                    return count;
                }
                return 0;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public int Execute_Non_Query_Stored_Procedure(string storedProcedureName, List<object> paramsArrays)
        {
            try
            {
                string errMsg = this.setConnectionStringDB();
                if (errMsg.Length == 0)
                {
                    SqlConnection myConn = this.connectionDatabase();
                    SqlCommand MyCommand = new SqlCommand(storedProcedureName, myConn);
                    MyCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i <= paramsArrays.Count - 1; i++)
                    {
                        MyCommand.Parameters.AddWithValue(string.Format("{0}{1}", "@Param", i + 1), paramsArrays[i]);
                    }
                    int count = MyCommand.ExecuteNonQuery();
                    myConn.Close();
                    return count;
                }
                return 0;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
    }
}